package cn.bdqn.dao.impl;
/**
 * ProviderDao的实现类
 */
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.bdqn.bean.Provider;
import cn.bdqn.dao.BaseDao;
import cn.bdqn.dao.ProviderDao;

public class ProviderDaoImpl implements ProviderDao {

	/**
	 * 添加供应商
	 */
	@Override
	public int addProvider(Connection con, Provider provider)
			throws SQLException {
		int row = 0;
		PreparedStatement ps = null;
		String sql = "insert into provider (id, provCode, provName, provDesc, "
				+ " contactName, phone, address, fax, createDate, createBy,isDelete ) "
				+ " values (sq_provider_id.nextval,?,?,?,?,?,?,?,sysdate,?,0) ";
		
		Object [] params = {provider.getProvCode(), provider.getProvName(),
				provider.getProvDesc(),provider.getContactName(),provider.getPhone(),
				provider.getAddress(),provider.getFax(),provider.getCreateBy()};
		
		row = BaseDao.execute(con, ps, sql, params);
		//关闭ps资源
		BaseDao.closeConnection( null, ps, null);
		return row;
	}

	/**
	 * 修改供应商
	 */
	@Override
	public int updateProvider(Connection con, Provider provider)
			throws SQLException {
		int row = 0;
		PreparedStatement ps = null;
		String sql = "update provider set provCode=?, provName=?, provDesc=?, "
				+ " contactName=?, phone=?, address=?, fax=?, modifyDate=sysdate,"
				+ " modifyBy=? where id=? ";
		
		Object [] params = {provider.getProvCode(), provider.getProvName(),
				provider.getProvDesc(),provider.getContactName(),provider.getPhone(),
				provider.getAddress(),provider.getFax(),provider.getModifyBy(),
				provider.getId()};
		
		row = BaseDao.execute(con, ps, sql, params);
		//关闭ps资源
		BaseDao.closeConnection( null, ps, null);
		return row;
	}

	/**
	 * 删除供应商
	 * 逻辑删除
	 */
	@Override
	public int deleteProvider(Connection con, Provider provider)
			throws SQLException {
		int row = 0;
		PreparedStatement ps = null;
		String sql = "update provider set isDelete=1, modifyDate=sysdate, "
				+ " modifyBy=? where id=? ";
		
		Object [] params = {provider.getModifyBy(),provider.getId()};
		
		row = BaseDao.execute(con, ps, sql, params);
		//关闭ps资源
		BaseDao.closeConnection( null, ps, null);
		return row;
	}

	/**
	 * 获取分页总记录数
	 */
	@Override
	public int getCount(Connection con, Provider provider) throws SQLException {
		int count = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer();
		sql.append("select count(1) count from provider where isDelete=0 ");
		ArrayList <Object> paraList = new ArrayList<Object>();
		//根据查询条件拼接sql语句并动态添加参数
		if(provider.getProvName()!=null && provider.getProvName() !="" ){
			sql.append(" and productName like ?");
			paraList.add("%"+ provider.getProvName()+"%");
		}
		//将StringBuffer 和list转换回来
		Object[] params = paraList.toArray();
		//执行sql语句
		rs = BaseDao.execute(con, ps, rs, sql.toString(), params);
		//结果只有一行数据
		if(rs.next()){
			count = rs.getInt(1);
		}
		//关闭ps资源
		BaseDao.closeConnection( null, ps, rs);
		return count;
	}
	/**
	 * 分页查询列表
	 */

	@Override
	public List<Provider> getList(Connection con, Provider provider,
			int pageIndex, int pageSize) throws SQLException {
		List<Provider> list = new ArrayList<Provider>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM  (SELECT ROWNUM rn, t1.* FROM "
				+ " (select * from provider where isDelete=0 ") ;
		ArrayList <Object> paraList = new ArrayList<Object>();
		//根据查询条件拼接sql语句并动态添加参数
		if(provider.getProvName()!=null && provider.getProvName() !="" ){
			sql.append(" and provName like ?");
			paraList.add("%"+ provider.getProvName()+"%");
		}
		//继续拼接分页查询后半截语句
		sql.append(" ORDER BY id) t1 WHERE ROWNUM <=?) t2 WHERE rn>=?") ;
		//处理分页参数
		int startWith = (pageIndex -1)  * pageSize + 1;
		int endWith = pageSize * pageIndex;
		//把分页参数赋值给paraList
		paraList.add(endWith);
		paraList.add(startWith);
		//将StringBuffer 和list转换回来
		Object[] params = paraList.toArray();
		//执行sql语句
		rs = BaseDao.execute(con, ps, rs, sql.toString(), params);
		//查询
		while(rs.next()){
			Provider pro = new Provider();
			pro.setId(rs.getInt("id"));
			pro.setProvCode(rs.getString("provCode"));
			pro.setProvName(rs.getString("provName"));
			pro.setProvDesc(rs.getString("provDesc"));
			pro.setPhone(rs.getString("phone"));
			pro.setContactName(rs.getString("contactName"));
			pro.setFax(rs.getString("fax"));
			pro.setCreateDate(rs.getTimestamp("createDate"));
			pro.setAddress(rs.getString("address"));
			list.add(pro);
		}
		//关闭ps资源
		BaseDao.closeConnection( null, ps, rs);
		return list;
	}

	/**
	 * 根据id获取provider的所有信息
	 */
	@Override
	public Provider getProviderById(Connection con, int id) throws SQLException {
		Provider provider = new Provider();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select * from provider where isDelete=0 and id=? ";
		Object [] params = {id };
		//执行sql语句
		rs = BaseDao.execute(con, ps, rs, sql, params);
		//结果只有一行数据
		if(rs.next()){
			provider.setId(rs.getInt("id"));
			provider.setProvCode(rs.getString("provCode"));
			provider.setProvName(rs.getString("provName"));
			provider.setProvDesc(rs.getString("provDesc"));
			provider.setPhone(rs.getString("phone"));
			provider.setContactName(rs.getString("contactName"));
			provider.setFax(rs.getString("fax"));
			provider.setCreateDate(rs.getTimestamp("createDate"));
			provider.setAddress(rs.getString("address"));
		}
		//关闭ps资源
		BaseDao.closeConnection( null, ps, rs);
		return provider;
	}

	/**
	 * 获取总列表
	 */
	@Override
	public List<Provider> getList(Connection con) throws SQLException {
		List<Provider> list = new ArrayList<Provider>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select * from provider where isDelete=0 " ;
		//执行sql语句
		rs = BaseDao.execute(con, ps, rs, sql);
		//查询
		while(rs.next()){
			Provider pro = new Provider();
			pro.setId(rs.getInt("id"));
			pro.setProvCode(rs.getString("provCode"));
			pro.setProvName(rs.getString("provName"));
			pro.setProvDesc(rs.getString("provDesc"));
			pro.setPhone(rs.getString("phone"));
			pro.setContactName(rs.getString("contactName"));
			pro.setFax(rs.getString("fax"));
			pro.setCreateDate(rs.getTimestamp("createDate"));
			pro.setAddress(rs.getString("address"));
			list.add(pro);
		}
		//关闭ps资源
		BaseDao.closeConnection( null, ps, rs);
		return list;
	}

}
